import sys
import os
import sqlite3, csv


def approval_ratings(conn, c):
    #create the sqlite table
    c.execute('''CREATE TABLE "approval_ratings_table" ("id" INTEGER PRIMARY KEY  NOT NULL , 
                                                        "president" VARCHAR NOT NULL ,
                                                        "start_date" VARCHAR NOT NULL , 
                                                        "end_date" VARCHAR NOT NULL , 
                                                        "approve" INTEGER NOT NULL , 
                                                        "disapprove" INTEGER NOT NULL , 
                                                        "unsure" INTEGER NOT NULL  )
              ''')
    conn.commit()
    
    reader = csv.reader(open('ApprovalRatings.csv', 'U'))
    id = 1
    for row in reader:
        president = row[0].strip()
        start_date = row[1].strip()
        end_date = row[2].strip()
        approve = int(row[3].strip())
        disapprove = int(row[4].strip())
        unsure = int(row[5].strip())
        stmt = "insert into approval_ratings_table values (%i, '%s', '%s', '%s', %i, %i, %i)" \
                     %  (id, president, start_date, end_date, approve, disapprove, unsure)
        print stmt
        c.execute( stmt )
        id = id + 1
    
    conn.commit()
 
def speech_text(conn, c):
    speech_dir = 'all_speeches'
    #create the sqlite table
    c.execute('''CREATE TABLE "speech_text_table" ("id" INTEGER PRIMARY KEY  NOT NULL , 
                                                   "speech" TEXT NOT NULL )
              ''')
    conn.commit()
    id = 1
    for fname in os.listdir(speech_dir):
        if '.speech' in fname:
            x = speech_dir+'/'+fname
            print x
            txt = open(x).read().replace('\'', '')
            stmt = "insert into speech_text_table values (%s, '%s')"  %  (fname.split('.')[0], txt)
            print stmt
            c.execute( stmt )
            id = id + 1
    conn.commit()
    
    print id
        
    
def main():
    # open the db
    conn = sqlite3.connect(os.path.join(os.getcwd(), 'speechviz4.sqlite'))
    c = conn.cursor()
#    approval_ratings(conn, c)
    speech_text(conn, c)
    c.close()
    
if __name__ == "__main__":
    main()
    